import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import javax.sql.*;
import java.io.*;
import javax.naming.*;

/**
 * @author Sai
 * Created by Sai on 2019-03-25.
 */
@SuppressWarnings("Duplicates")
@WebServlet("jdbc")
public class JdbcServlet extends HttpServlet {


    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) {
        try {
        // 每次执行都新建一个连接
        InitialContext context = new InitialContext();
        DataSource dataSource =
                (DataSource) context.lookup("jdbc/DSTest");
        Connection conn = null;
        Statement stmt = null;
            //3.通过数据库的连接操作数据库，实现增删改查
        conn = dataSource.getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS `student` (" +
                            "  `id` int(11) unsigned NOT NULL AUTO_INCREMENT," +//auto_in....自动有生产id，自己加会报错
                            "  `course` varchar(100) NOT NULL," +
                            "  `score` int(11) NOT NULL," +
                            "  PRIMARY KEY (`id`)" +
                            ");"
            );
            stmt.executeUpdate(
                    "insert into `student` (`course`,`score`) values ( '计算机', 27);" +
                            "insert into `student` (`course`,`score`) values ( '美食', 75);"+
                                "insert into `student` (`course`,`score`) values ( '美术', 85);"
            );
            ResultSet rs = stmt.executeQuery("select * from student");//选择import java.sql.ResultSet;

            // 遍历每行记录
            while (rs.next()) {
                //如果结果集中有数据，就会循环打印出来
                resp.setContentType("contentType= text/html;charset=UTF-8");
              try{  resp.getWriter().println(rs.getInt("id")+","+rs.getString("course") + "," + rs.getInt("score"));}catch (IOException e){e.getMessage();}
            }

        } catch (SQLException e) {e.getMessage(); }
          catch (NamingException e){e.getMessage();}

    }
}
